So we've created calculations in two different spots within our Excel document.
We've created calculations inside of the pivot table.
We did an average of the freight and we did the exact same thing inside of our data model through the
power pivot tool.
Now one of the benefits of creating calculations such as the average freight or the counter shift via
inside of the data model is we can now create K.P. eyes or key performance indicators based on those
calculations.
So for example you know I want to be able to take this average freight here which is $72 and roughly
24 cents.
And get the average nut for the entire set of orders but perhaps for various countries you know what
was the average for USA versus Canada versus Brazil versus France and so on.
Right now I want to set up some thresholds and I want to say if we've got more than $60 that are going
out to any one country as an average freight then we're doing something wrong that should get a low
mark.
If we're below $15 15 or below going out average to any country then we're doing really well and we
want the lower rate.
This will reward our customers right.
So we're going to set up a keep height that holds some thresholds a high value a low value and get back
some indicators some little graphical indicators letting us know how well that average rate for that
country is performing within our thresholds.
So take a look this is how you create KPI key performance indicators within your Excel data models through
the power pivot tool.
So first that you've got to have the calculation.
So I've already created the average free $72 and roughly 24 cents.
I mean right click on that new calculation.
And one of my Right-Click options here is to create a KPI.
I'll give that a click.
This is going to open up my interface to build a set up.
The actual KPI is essentially where are you going to set up those thresholds.
Right.
What's your your maximum value what's your minimum value.
What's your goal essentially where are you trying to get to May and your bottom value right where you
don't really want to be.
So you've got two ways to build that to find the target.
This is where we want to be.
I can do it based on a calculated field meaning another calculated field.
Back here inside of my data model or I could do it based off of an absolute value.
Now the only other calculated field that I have here is the distinct count of ship via methods.
So I'm not going to use that.
So I mean it's an absolute value defaulted me to 100.
I can see that my range kind of my threshold down below ranges from 0 to 40.
If so the average rate is somewhere between 0 and 40.
I'm in the red if it's above 40 below 80 then we're in the yellow if it's above 80 then I'm in the green
.
Now for me you know I think to my customers right customers come first.
I shouldn't be charging them more for shipping their products right customers.
You'd be happier if I charge them less.
So really I want this to go the other way around.
So Down below I can swap the way that I portray my scale within these thresholds.
Currently I'm going from red yellow to green.
I'm going to go from green yellow to red.
I want to swat that threshold.
So now I've got a if it's below 40 then I'm in the green if it's between 40 and 80.
I'm in the yellow if it's above 80.
I'm in the the red here.
Now I'm going adjust these values.
I mean to say you know if your average is average for a country I'd say if we're $60 or less than we're
in the green and we'll say it war.
I don't know I will say I cannot again adjust this either by taking the values in they're dragging these
little icons musée for $90.
It's 90 there.
And you know at 60 I think 60 sounds good.
Will view it as such.
All right.
So if I'm $0.60 being me me me me.
We're doing good.
Our customers are happy we're not charging them as much for shipping it for between sixty nine.
We're in yellow.
And if we're above 90 as an average for the country then we're doing poor.
Inside of there.
All right.
I'm going to go ahead and.
OK.
So I've now created the KPI inside of there Agassi little KPI marker down there next my calculation
.
But we're really not taking full advantage of this just yet.
All we've done is created the calculation and set up the KPI.
Now let's actually put this into our report.
So I'm going to close my power pivot window.
We'll get back to sell.
There's my pivot table.
I've already got the countries out there I've already got the average of freight but now I want to bring
in the KPI so I can see how well we're doing.
So I'm going to hop over to my pivot table field just open up the order info table and scroll down.
I've now got my average of freight's with the KPI.
Open that up.
I've got three things in there the value which is what I already have out here.
I got the goal which was the goal marker that I put back inside of the keep the threshold area and I've
got the status the status is the actual KPI the indicator that shows us how well we're doing.
So I'm going to turn that went on a turn on status and I've now got a KPI based off of that KPI within
the data model.
So remember if there was 60 or below.
We're in the green there's green and green and green green green there differ between 60 and 90 we're
in the yellow.
And if we're above 90 we've got the red a key performance indicator based on a calculation with in our
data model and because of the way that we are displaying it here we're getting KPI is based on the average
rate for each country.
Try this out.
Jump back into your data model create the calculation average freight if you don't have it already.
Right click on it and create a KPI.
You decide on the numbers.
I felt these were good because we don't really have a huge scale inside of here.
So 60 below create 60 to 90 and above 90.
We're not doing well.
So try this out set up your KPI within your data model and then incorporate it into your pivot table
.